CREATE OR REPLACE package oravc_job as

/*
<main_description>
  creator: Andrey Tsyganov
     date: August, 2011
  contact: atsygan@gmail.com, A.Tsyganov@rug.nl
  (c) 2011, all rights recerved

  Developer of this package is NOT responsible in case of any change of this coe by its users!
  and also NOT responsible for any bugs in the functionality of the package, however support CAN be provided

  This package is dedicated to the OraVC scheduler job run.
  Put to a separate package due to the AUTHID CURRENT_USER option in the ORAVC_ENGINE package.
</main_description>

*/

FUNCTION get_C_SYNONYM_FOR_SCHEDULER  RETURN VARCHAR2;
FUNCTION get_C_ORAVC_REFRESH_JOB      RETURN VARCHAR2;


/*
<comments>
  <procedure>
    oravc_start_plvc_job
  </procedure>

  <description>
    This procedure start refresh job for the Verison Control
    Parameter p_setcleanvers_olderthen defines the number of days to delete versions as old
    If p_setcleanvers_olderthen parameter is not null then
      cleans old data lower {sysdate - p_setcleanvers_olderthen_day}
      but never deletes last version of the object
    by default p_setcleanvers_olderthen_day is null
  </description>

  <parameters>
    <IN>
      [p_refresh_time_min]              [number of minutes for interval in refresh process]   [NUMBER]
      [p_setcleanvers_olderthen_day]    [number of days which indicates old version]          [NUMBER]  [NULL]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
PROCEDURE oravc_start_plvc_job(
             p_refresh_time_min                      NUMBER
            ,p_setcleanvers_olderthen_day            NUMBER := NULL
          );

/*
<comments>
  <procedure>
    oravc_drop_plvc_job
  </procedure>

  <description>
    This procedure deletes refresh job for the Verison Control
  </description>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
PROCEDURE oravc_drop_plvc_job;

/*
<comments>
  <procedure>
    oravc_run_manual_refresh
  </procedure>

  <description>
    This procedure manually starts the Version Controll repository refresh
    It runs only ones per call.. 
  </description>

  <version>
    [1.0] [28-01-2014] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
PROCEDURE oravc_run_manual_refresh;

end oravc_job;
/


CREATE OR REPLACE package body oravc_job as

C_SYNONYM_FOR_SCHEDULER                          CONSTANT VARCHAR2(255) := 'ATS_ORAVC';
C_ORAVC_REFRESH_JOB                              CONSTANT VARCHAR2(255) := 'ORAVC_ENGINE$$$$REFRESH_JOB';
C_PACKAGE_NAME                                   CONSTANT VARCHAR2(255) := 'ORAVC_JOB';

FUNCTION get_C_SYNONYM_FOR_SCHEDULER  RETURN VARCHAR2 IS BEGIN RETURN C_SYNONYM_FOR_SCHEDULER;  END;
FUNCTION get_C_ORAVC_REFRESH_JOB      RETURN VARCHAR2 IS BEGIN RETURN C_ORAVC_REFRESH_JOB;      END;

/*
<comments>
  in package specification

</comments>
*/
PROCEDURE oravc_start_plvc_job(
             p_refresh_time_min                      NUMBER
            ,p_setcleanvers_olderthen_day            NUMBER := NULL
          )
  AS

  l_setcleanvers_olderthen_day                       VARCHAR2(255);
BEGIN
  IF p_setcleanvers_olderthen_day IS NULL THEN
    l_setcleanvers_olderthen_day := 'NULL';
  ELSE
    l_setcleanvers_olderthen_day := TO_CHAR(p_setcleanvers_olderthen_day);
  END IF;

  DBMS_SCHEDULER.create_job (
    job_name        => C_ORAVC_REFRESH_JOB,
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN ' || C_SYNONYM_FOR_SCHEDULER || '.oravc_main_vc(NULL, NULL, NULL, ' || l_setcleanvers_olderthen_day || '); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=MINUTELY; INTERVAL=' || p_refresh_time_min,
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job for Oracle Version Controll engine'
  );
END oravc_start_plvc_job;

/*
<comments>
  in package specification

</comments>
*/
PROCEDURE oravc_drop_plvc_job AS
BEGIN
  DBMS_SCHEDULER.STOP_JOB (
    job_name => C_ORAVC_REFRESH_JOB
  );

  DBMS_SCHEDULER.drop_job (
    job_name => C_ORAVC_REFRESH_JOB
  );
EXCEPTION
  WHEN OTHERS THEN
    DBMS_SCHEDULER.drop_job (
      job_name => C_ORAVC_REFRESH_JOB
    );
END oravc_drop_plvc_job;


/*
<comments>
  in package specification

</comments>
*/
PROCEDURE oravc_run_manual_refresh AS
  l_function_name                    VARCHAR2(255) := 'oravc_run_manual_refresh';

BEGIN
  oravc_engine.oravc_main_vc(null,null,null,null);  
END oravc_run_manual_refresh;

end oravc_job;
/
